<!--
	o   Will provide all the links to display various reports 
-->


<?php

$ErrorCode = "-1";



echo "<p>Reports Page</p>";
//	INITIATE DATABASE CONNECTION
include('connect.php');

	if($ErrorCode == -1)
	{
		if($_POST["Reportsuser"] == "LUSERNAME")
		{
			$result = mysql_query("SELECT NAME,USERNAME,EMAIL_ADDRESS,MEANING,BLACKMARK,REGISTRATION_DATE,R.MEANING 
			                       FROM USERS U,HSOBO_REFERENCE_CODES R
                                   WHERE U.USER_TYPE = R.CODE_VALUE and R.CODE_TYPE = 'USER_TYPE' and STATUS = 'A' order by NAME");
		}elseif($_POST["Reportsuser"] == "LUSERREGDATE")
		{
			$result = mysql_query("SELECT NAME,USERNAME,EMAIL_ADDRESS,MEANING,BLACKMARK,REGISTRATION_DATE,REGISTRATION_DATE,R.MEANING 
			                       FROM USERS U,HSOBO_REFERENCE_CODES R
                                   WHERE U.USER_TYPE = R.CODE_VALUE and R.CODE_TYPE = 'USER_TYPE' and STATUS = 'A' order by REGISTRATION_DATE");
		}elseif($_POST["Reportsuser"] == "LUSERTYPE")
		{
			$result = mysql_query("SELECT NAME,USERNAME,EMAIL_ADDRESS,MEANING,BLACKMARK,REGISTRATION_DATE,REGISTRATION_DATE,R.MEANING 
			                       FROM USERS U,HSOBO_REFERENCE_CODES R
                                   WHERE U.USER_TYPE = R.CODE_VALUE and R.CODE_TYPE = 'USER_TYPE' and STATUS = 'A' order by R.MEANING");
		}elseif($_POST["Reportsrevenue"] == "LREVENUEDATE")
		{
			$result = mysql_query("select trans_date CGROUP,sum(amount) AMOUNT from TRANSACTIONS WHERE PAID = 'YES' group by 1 order by 1 desc");
		}elseif($_POST["Reportsrevenue"] == "LREVENUEFEE")
		{
			$result = mysql_query("select f.description CGROUP,sum(t.amount) AMOUNT from TRANSACTIONS t ,FEES f WHERE f.fee_code = t.fee_code and t.PAID = 'YES'
									group by 1 order by 1 asc");
		}elseif($_POST["Reportslisting"] == "LLISTTYPE")
		{
			$result = mysql_query("SELECT H.MEANING,SELLING_PRICE,HITS,LISTING_DATE,EXPIRY_DATE   
									FROM LISTS l INNER JOIN PROPERTY p on p.PRTID = l.PRTID inner join HSOBO_REFERENCE_CODES H on H.code_value = p.property_type  
									LEFT JOIN (select prtid ,count(*) as ROOMCOUNT from ROOMS group by prtid ) r on r.prtid=p.prtid
									where expiry_date >= curdate()
									order by 1,2");
		}elseif($_POST["Reportslisting"] == "LLISTDATE")
		{
			$result = mysql_query("SELECT H.MEANING,SELLING_PRICE,HITS,LISTING_DATE,EXPIRY_DATE   
									FROM LISTS l INNER JOIN PROPERTY p on p.PRTID = l.PRTID inner join HSOBO_REFERENCE_CODES H on H.code_value = p.property_type  
									LEFT JOIN (select prtid ,count(*) as ROOMCOUNT from ROOMS group by prtid ) r on r.prtid=p.prtid
									where expiry_date >= curdate()
									order by 4,1,2");
		}elseif($_POST["Reportslisting"] == "LLISTEXPIRY")
		{
			$result = mysql_query("SELECT H.MEANING,SELLING_PRICE,HITS,LISTING_DATE,EXPIRY_DATE   
									FROM LISTS l INNER JOIN PROPERTY p on p.PRTID = l.PRTID inner join HSOBO_REFERENCE_CODES H on H.code_value = p.property_type  
									LEFT JOIN (select prtid ,count(*) as ROOMCOUNT from ROOMS group by prtid ) r on r.prtid=p.prtid
									where expiry_date >= curdate()
									order by 5,1,2");
		}
	
		if(!$result)
		{
			echo '<br />ERROR535 ';
			die('SQL Command Error: ' . mysql_error());
		}
	}




// revenues
echo ('	<form name="reports" id="reports" method="POST" action="index.php?page=reports">
		<TABLE border=2>
		<TR><TD colspan=10 class="center">Revenues</TD></TR>
		<TR>
		<TR><TD>Order by</TD><TD colspan=2>'. create_combobox("REVENUE_REPORT","Reportsrevenue",false,$_POST["Reportsrevenue"]).'<a href="#" onclick="document.search.submit();"></a><input type="submit" value="Submit"/></TD></TR>
		</TABLE>
		</form>');

// users
echo ('	<form name="reports" id="reports" method="POST" action="index.php?page=reports">
		<TABLE border=2>
		<TR><TD colspan=10 class="center">Users</TD></TR>
		<TR>
		<TR><TD>Order by</TD><TD colspan=2>'. create_combobox("USER_REPORT","Reportsuser",false,$_POST["Reportsuser"]).'<a href="#" onclick="document.search.submit();"></a><input type="submit" value="Submit"/></TD></TR>
		</TABLE>
		</form>');

		// Listings
echo ('	<form name="reports" id="reports" method="POST" action="index.php?page=reports">
		<TABLE border=2>
		<TR><TD colspan=10 class="center">Listings</TD></TR>
		<TR>
		<TR><TD>Order by</TD><TD colspan=2>'. create_combobox("LISTING_REPORT","Reportslisting",false,$_POST["Reportslisting"]).'<a href="#" onclick="document.search.submit();"></a><input type="submit" value="Submit"/></TD></TR>
		</TABLE>
		</form>');


//Search RESULT
if(($_POST["Reportsuser"] == ("LUSERNAME")) || ($_POST["Reportsuser"] == ("LUSERREGDATE")) || ($_POST["Reportsuser"] == ("LUSERTYPE")))
{
	echo '<br /><br />Report results for users<hr>';
	echo ('	
	<TABLE border=2>
		<TR>
			<TH>Name</TH>
			<TH>User name</TH>
			<TH>Email</TH>
			<TH>Type</TH>
			<TH>Black Mark</TH>
			<TH>Registration date</TH>
			<TH>User type</TH>
		</TR>
		');
}elseif($_POST["Reportsrevenue"] == "LREVENUEDATE")
{
	echo '<br /><br />Report results for revenues<hr>';
	echo ('	
	<TABLE border=2>
		<TR>
			<TH>Date</TH>
			<TH>Revenue</TH>
		</TR>
		');
}elseif($_POST["Reportsrevenue"] == "LREVENUEFEE")
{
	echo '<br /><br />Report results for revenues<hr>';
	echo ('	
	<TABLE border=2>
		<TR>
			<TH>Fee</TH>
			<TH>Revenue</TH>
		</TR>
		');
}elseif($_POST["Reportslisting"] == "LLISTDATE" || $_POST["Reportslisting"] == "LLISTTYPE" || $_POST["Reportslisting"] == "LLISTEXPIRY")
{
	echo '<br /><br />Report results for listings<hr>';
	echo ('	
	<TABLE border=2>
		<TR>
			<TH>Property Type</TH>
			<TH>Selling price</TH>
			<TH>Hits</TH>
			<TH>Listing date</TH>
			<TH>Expiry date</TH>
		</TR>
		');
}


	if($ErrorCode == -1)
	{
    if(($_POST["Reportsuser"] == ("LUSERNAME")) || ($_POST["Reportsuser"] == ("LUSERREGDATE")) || ($_POST["Reportsuser"] == ("LUSERTYPE")))
	{	while($row = mysql_fetch_assoc($result))
		{
		  echo('  <TR>
				  <TD class="center">' . $row['NAME'] . '</TD>
				  <TD class="center">' . $row['USERNAME'] . '</TD>
				  <TD class="center">' . $row['EMAIL_ADDRESS'] . '</TD>
				  <TD class="center">' . $row['MEANING'] . '</TD>
				  <TD class="center">' . $row['BLACKMARK'] . '</TD>
				  <TD class="center">' . $row['REGISTRATION_DATE'] . '</TD>
				  <TD class="center">' . $row['MEANING'] . '</TD>
				  </TR>
		  ');
		}
	}elseif(($_POST["Reportsrevenue"] == ("LREVENUEDATE")) || ($_POST["Reportsrevenue"] == ("LREVENUEFEE")))
	{	while($row = mysql_fetch_assoc($result))
		{
		  echo('  <TR>
				  <TD class="center">' . $row['CGROUP'] . '</TD>
				  <TD class="center">' . $row['AMOUNT'] . '$</TD>
				  </TR>
		  ');
		}

	}elseif(($_POST["Reportslisting"] == ("LLISTDATE")) || ($_POST["Reportslisting"] == ("LLISTTYPE"))|| ($_POST["Reportslisting"] == ("LLISTEXPIRY")))
	{	while($row = mysql_fetch_assoc($result))
		{
		  echo('  <TR>
				  <TD class="center">' . $row['MEANING'] . '</TD>
				  <TD class="center">' . $row['SELLING_PRICE'] . '$</TD>
				  <TD class="center">' . $row['HITS'] . '</TD>
				  <TD class="center">' . $row['LISTING_DATE'] . '</TD>
				  <TD class="center">' . $row['EXPIRY_DATE'] . '</TD>
				  </TR>
		  ');
		}

	}
	}
echo '</TABLE>';

//	CLOSE DATABASE CONNECTION
include('disconnect.php');

?>

